<?php
namespace app\controller;

use app\item\Record;
use ArrayObject;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use app\tools\Upload;
use script\Config;
use script\Db;
use script\Http;

class Excel
{
    public function middle ()
    {
        // $data = Config::array();
        $data = Http::get('params');
        // 11111111111
        return $data;
    }

    public function record()
    {
        $upload = new Upload(['name'=> 'files', 'type' => ['image/jpeg', 'text/csv', 'text/plain']]);
        $res = $upload->upload();
        $record = new Record($res);
        $data = $record->run();
        // $data = [
        //     'alipay' => '支付宝',
        //     'wechat' => '微信支付',
        // ];
        // var_dump($data);
        // die;
        $db = Db::connect()->table('p_tizu')->insert($data);
        // die;
        // var_dump($db);
    }

    public function alipay(Array $data)
    {
        $res = [];
        foreach ($data as $key => $value) {
            $str = '';
            foreach ($value as $k => $v) {
                if (!empty($v)) {
                    $str .= $v;
                }
            }
            $res[$key] = stristr($str, ',') ? explode(',', trim($str)) : trim($str);
        }

        $res = array_slice($res, 0, count($res)-7);
        // 收支类型
        $io_type = [
            1 => '支出',
            2 => '收入',
            3 => '资金转移'
        ];
        $lists = [];
        foreach ($res as $key => $value) {
            // 支付宝
            if (is_array($value)) {
                $io = array_search(trim($value[10]), $io_type) ? 
                        array_search(trim($value[10]), $io_type) : 
                        array_search(trim($value[15]), $io_type);
                // 交易成功则写入数据
                if ($io) {
                    $itme = [];
                    // 状态
                    // $itme['status'] = trim($value[15]);
                    // 收支（收入与支出）
                    $itme['io'] = $io;
                    // 金额
                    $itme['price'] = trim($value[9]);
                    // 描述
                    $itme['desc'] = trim($value[8]);
                    // 标题
                    $itme['title'] = trim($value[7]);
                    // 分类
                    // $itme['cat'] = trim($value[15]);
                    // 交易时间
                    $itme['pay_time'] = trim($value[4]);
                    // 交易日期
                    $itme['pay_date'] = date('Y-m-d', strtotime(trim($value[4])));
                    array_push($lists, $itme);
                }   
            }
        }
        return $lists;
    }

    public function wechat(Array $data)
    {
        echo '<pre>';
        
        foreach ($data as $key => $value) {
            foreach ($value as $k => $v) {
                if (stristr($v, '¥')) {       
                    $value[$k] = str_replace('¥', '', str_replace(',', '', $v));
                }
            }
            $str = implode(',', $value);
            $res[$key] = stristr($str, ',') ? explode(',', trim($str)) : trim($str);
        }
        $res = array_slice($res, 16, count($res)-1);

        // die;
        // 收支类型
        $io_type = [
            1 => '支出',
            2 => '收入',
            3 => '资金转移'
        ];
        $lists = [];
        foreach ($res as $key => $value) {
            // 微信
            if (is_array($value)) {
                // var_dump($value[2]);
                $io = array_search(trim($value[4]), $io_type) ? 
                        array_search(trim($value[4]), $io_type) : 
                        3;
                // $price = stristr($value[5], '.') ? $value[5] : $value[5].$value[6];
                // 交易成功则写入数据
                if ($io) {
                    $itme = [];
                    // 状态
                    // $itme['status'] = trim($value[15]);
                    // 收支（收入与支出）
                    $itme['io'] = $io;
                    // 金额
                    // $itme['price'] = trim($value[5]);
                    $itme['price'] = $value[5];
                    // 描述
                    $itme['desc'] = trim($value[3]);
                    // 标题
                    $itme['title'] = trim($value[2]);
                    // 分类
                    // $itme['cat'] = trim($value[15]);
                    // 交易时间
                    $itme['pay_time'] = trim($value[0]);
                    // 交易日期
                    $itme['pay_date'] = date('Y-m-d', strtotime(trim($value[0])));
                    array_push($lists, $itme);
                }   
            }
        }
        
        return $lists;
        
    }

    public function aliin()
    {
        
        $inputFileName = './public/alipay_record_20190324_1548_1.csv';
        $inputFileName = './public/alipay_record_20190324_1548_1.txt';
        // $inputFileName = './public/微信支付账单(20191006-20191221)(1).csv';
        $inputFileType = 'Csv';

        $record = new Record($inputFileName);
        var_dump($record);die;

        var_dump(ucfirst(pathinfo($inputFileName, PATHINFO_EXTENSION)));die;
        // 建立表格阅读器
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        if (stristr($inputFileName, 'alipay')) {
            $reader->setInputEncoding('GBK');
        }
        
        // 加载表格
        $spreadsheet = $reader->load($inputFileName);
    
        $data = $spreadsheet
            ->getSheet(0) // 指定第一个工作表为当前
            ->toArray();  // 转为数组
        
        // 账单类型
        $record_type = [
            'alipay' => '支付宝',
            'wechat' => '微信支付',
        ];
        if (isset($data[0][0])) {
            foreach ($record_type as $k => $v) {
                if (stristr($data[0][0], $v)) {
                    $type = $k;
                    break;
                }
            }
        }
        $data = $this->$type($data);
        echo '<pre>';
        var_dump($type, $data);die;

        // 账单类型
        $record_type = [
            'alipay' => '支付宝',
            'wechat' => '微信支付',
        ];

        // 收支类型
        $io_type = [
            1 => '支出',
            2 => '收入',
            3 => '资金转移'
        ];
        
        
        $res = [];
        foreach ($data as $key => $value) {
            $str = '';
            foreach ($value as $k => $v) {
                if (!empty($v)) {
                    $str .= $v;
                }
            }
            $res[$key] = stristr($str, ',') ? explode(',', trim($str)) : trim($str);
        }

        $res = array_slice($res, 0, count($res)-7);
        $lists = [];
        foreach ($res as $key => $value) {
            if ($key == 0) {
                foreach ($record_type as $k => $v) {
                    if (stristr($value, $v)) {
                        $type = $k;
                        break;
                    }
                }
            }

            // 支付宝
            if (isset($type) && $type == 'alipay' && is_array($value)) {
                $io = array_search(trim($value[10]), $io_type) ? 
                        array_search(trim($value[10]), $io_type) : 
                        array_search(trim($value[15]), $io_type);
                // 交易成功则写入数据
                if ($io) {
                    $itme = [];
                    // 状态
                    // $itme['status'] = trim($value[15]);
                    // 收支（收入与支出）
                    $itme['io'] = $io;
                    // 金额
                    $itme['price'] = trim($value[9]);
                    // 描述
                    $itme['desc'] = trim($value[8]);
                    // 标题
                    $itme['title'] = trim($value[7]);
                    // 分类
                    // $itme['cat'] = trim($value[15]);
                    // 交易时间
                    $itme['pay_time'] = trim($value[4]);
                    // 交易日期
                    $itme['pay_date'] = date('Y-m-d', strtotime(trim($value[4])));
                    array_push($lists, $itme);
                }
                
            }

            // 微信
            if (isset($type) && $type == 'wechat' && is_array($value)) {
                var_dump($value);die;
                // 交易成功则写入数据
                if ($io) {
                    $itme = [];
                    // 状态
                    // $itme['status'] = trim($value[15]);
                    // 收支（收入与支出）
                    $itme['io'] = $io;
                    // 金额
                    $itme['price'] = trim($value[9]);
                    // 描述
                    $itme['desc'] = trim($value[8]);
                    // 标题
                    $itme['title'] = trim($value[7]);
                    // 分类
                    // $itme['cat'] = trim($value[15]);
                    // 交易时间
                    $itme['pay_time'] = trim($value[4]);
                    // 交易日期
                    $itme['pay_date'] = date('Y-m-d', strtotime(trim($value[4])));
                    array_push($lists, $itme);
                }
                
            }
            
            
        }
        var_dump($lists);die;
    }

    public function aliout()
    {
        $inputFileName = './public/alipay_record_20190324_1548_1.csv';
        // $inputFileName = './public/01-09.xls';
        // $inputFileType = 'Xls';

        var_dump(pathinfo($inputFileName, PATHINFO_EXTENSION));die;
        $inputFileType = 'Csv';
        // 建立表格阅读器
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        $reader->setInputEncoding('GBK');
        // 加载表格
        $spreadsheet = $reader->load($inputFileName);
        

        $currSheet = $spreadsheet->getSheet(0);

        $mergeCells = $currSheet->getMergeCells();
        /* 取得最大的列号 */
        $columnH = $currSheet->getHighestColumn();
        /* 兼容原逻辑，循环时使用的是小于等于 */
        $columnCnt = Coordinate::columnIndexFromString($columnH);
        $cellName = Coordinate::stringFromColumnIndex($columnCnt);
    

        /* 获取总行数 */
        $rowCnt = $currSheet->getHighestRow();
        
        


        $data = $spreadsheet
            ->getSheet(0) // 指定第一个工作表为当前
            ->toArray();  // 转为数组
            echo '<pre>';
            var_dump($data);die;

        //建立Csv对象
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        $reader->setInputEncoding('GBK'); //this line would internally convert chinese gbk to utf-8 encoding
        $spreadsheet = $reader->load($inputFileName);
        $data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        echo '<pre>';

        var_dump($data);die;
    }

    public function out()
    {
        $inputFileName = './public/01-09.xls';
        

        $inputFileType = 'Xls';
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);

        $worksheet = $spreadsheet->getActiveSheet();

        $rowCnt = $worksheet->getHighestRow();

        for ($i=2; $i < $rowCnt+1; $i++) { 
            $date = $worksheet->getCell('C'.$i)->getFormattedValue();
            $str = substr($date, 0, 8);
            $cur_time = date('Y/m/d', strtotime($str));
           
            $worksheet->getCell('C'.$i)->setValue($cur_time);
        }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $inputFileType);


        $filename = date('Ymdhis').'-'.uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $inputFileType);
        $writer->save('php://output');

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

    public function index()
    {
        echo '<pre>';
        // $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');

        $inputFileName = './public/A058171TB_ND37989000000007675604010_CN000_20191126164652_00658486_resp.xls';

        /** Load $inputFileName to a Spreadsheet Object  **/
        // $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

        $inputFileType = 'Xls';
        //    $inputFileType = 'Xlsx';
        //    $inputFileType = 'Xml';
        //    $inputFileType = 'Ods';
        //    $inputFileType = 'Slk';
        //    $inputFileType = 'Gnumeric';
        //    $inputFileType = 'Csv';

        /**  Create a new Reader of the type defined in $inputFileType  **/
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        /**  Load $inputFileName to a Spreadsheet Object  **/
        $spreadsheet = $reader->load($inputFileName);

        $worksheet = $spreadsheet->getActiveSheet();

        // $spreadsheet->getActiveSheet()->getCell('B8')->setValue('Some value');
        // $data = $worksheet->getCell('B4')->getFormattedValue();

        $rowCnt = $worksheet->getHighestRow();

        for ($i=2; $i < $rowCnt+1; $i++) { 
            $date = $worksheet->getCell('C'.$i)->getFormattedValue();
            $cur_time = date('Y年m月d H:i:s', strtotime($date));
            $worksheet->getCell('C'.$i)->setValue($cur_time);
        }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $inputFileType);
        // $writer->save('php://output');
        var_dump($writer);die;
        $filename = '成绩表.Xls';
        // header('Content-Type: application/vnd.ms-excel');
        // header('Content-Disposition: attachment;filename="'.$filename.'"');
        // header('Cache-Control: max-age=0');

        header("Content-type: text/html; charset=utf-8");
         // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        //  header('Content-Disposition: attachment;filename="' . $filename);
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
         // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
 
         // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        // $reader->save('php://output');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $inputFileType);
        $writer->save('php://output');
    }

    
    
}